﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using Common;
using System.Globalization;

namespace DataAccess {
  /// <summary>
  /// Data Access Layer for application settings
  /// </summary>
  public class AppDal {

    #region - Public methods -

    /// <summary>
    /// Gets the application data.
    /// </summary>
    public void GetAppData( ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectCommand( connection ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {
                SettingsSingleton.Settings.AppId = reader.GetInt32( reader.GetOrdinal( "ID" ) );
                SettingsSingleton.Settings.FirstRun = reader.GetValue( reader.GetOrdinal( "FirstRun" ) ) == DBNull.Value ? false : Convert.ToBoolean( reader.GetValue( reader.GetOrdinal( "FirstRun" ) ), CultureInfo.CurrentCulture );
                SettingsSingleton.Settings.SmtpServerName = reader.GetValue( reader.GetOrdinal( "SMTPServerName" ) ).ToString( );
                SettingsSingleton.Settings.SmtpUserName = reader.GetValue( reader.GetOrdinal( "SMTPUserName" ) ).ToString( );
                SettingsSingleton.Settings.SystemEmail = reader.GetValue( reader.GetOrdinal( "SystemEmail" ) ).ToString( );
                SettingsSingleton.Settings.SmtpPort = reader.GetValue( reader.GetOrdinal( "SmtpPort" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "SmtpPort" ) );
              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
    }

    /// <summary>
    /// Sets the application data.
    /// </summary>
    public void SetAppData( ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          try {
            SQLiteCommand command = CreateUpdateCommand( connection );
            command.Transaction = transaction;
            command.ExecuteNonQuery( );
            transaction.Commit( );
          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
    }

    #endregion

    #region - Private methods -

    private SQLiteCommand CreateSelectCommand( SQLiteConnection connection ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"SELECT ID, FirstRun, SMTPServerName, 
                              SMTPUserName, SystemEmail, SmtpPort                              
                             FROM Application";
      return command;
    }

    private SQLiteCommand CreateUpdateCommand( SQLiteConnection connection ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE Application SET FirstRun = @firstrun, SMTPServerName = @smtpservername,
                              SMTPUserName = @smtpusername, SystemEmail = @systememail, SmtpPort = @smtpport 
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@firstrun", SettingsSingleton.Settings.FirstRun );
      command.Parameters.AddWithValue( "@smtpservername", SettingsSingleton.Settings.SmtpServerName );
      command.Parameters.AddWithValue( "@smtpusername", SettingsSingleton.Settings.SmtpUserName );
      command.Parameters.AddWithValue( "@systememail", SettingsSingleton.Settings.SystemEmail );
      command.Parameters.AddWithValue( "@smtpport", SettingsSingleton.Settings.SmtpPort );
      command.Parameters.AddWithValue( "@id", SettingsSingleton.Settings.AppId );
      return command;
    }

    #endregion

  }
}
